package dao;

import config.JDBCDataSource;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import util.JDBCUtils;
import util.ReflectUtils;
import util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Vector;

/**
 * <p>
 * 基本DAO
 * 封装了基本的增删改查方法
 * </p>
 *
 * @author caoqianfan
 * @date 2024/7/15 下午2:33
 */
@Slf4j
@Data
public class _BaseDao<T> {

    private final Class<T> clazz;

    private final JDBCDataSource dataSource;

    private final Connection connection;

    public _BaseDao() {
        // 使用反射获取泛型类型
        this.clazz = getGenericClass(getClass());
        this.dataSource = new JDBCDataSource();
        this.connection = dataSource.getConnection();
    }

    private static <T> Class<T> getGenericClass(Class<?> clazz) {
        while (clazz != null) {  // 确保clazz不为null
            // 获取当前类的直接超类
            Type genericSuperclass = clazz.getGenericSuperclass();

            // 如果超类是ParameterizedType，说明我们找到了泛型定义的类
            if (genericSuperclass instanceof ParameterizedType paramType) {
                Type[] typeArguments = paramType.getActualTypeArguments();

                // 我们通常期望第一个类型参数是我们的泛型类型T
                Type typeArgument = typeArguments[0];

                // 确保类型参数是Class类型
                if (typeArgument instanceof Class) {
                    @SuppressWarnings("unchecked")
                    Class<T> type = (Class<T>) typeArgument;
                    return type;
                } else {
                    // 如果不是Class类型，抛出异常
                    throw new IllegalArgumentException("Type argument is not a class: " + typeArgument);
                }
            }

            // 如果当前类不是泛型定义的类，继续向上转型
            clazz = clazz.getSuperclass();
        }

        // 如果没有找到泛型类型参数，抛出异常
        throw new IllegalArgumentException("No type parameter found in the hierarchy of " + clazz.getName());
    }

    /**
     * 新增
     *
     * @param obj obj
     */
    public void save(T obj) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        StringBuilder sql = new StringBuilder("Insert INTO " + tableName + "(");
        Map<String, String> columns = ReflectUtils.getColumnName(clazz);
        List<String> columnsStringList = new Vector<>(columns.values());
        List<String> selectedColumnList = new Vector<>();
        for (String column : columnsStringList) {
            Object value = ReflectUtils.getPkValueByName(obj, column);
            if (value != null) {
                sql.append(column).append(",");
                selectedColumnList.add(column);
            }
        }
        sql.deleteCharAt(sql.length() - 1)
                .append(") values (")
                .append("?,".repeat(selectedColumnList.size()))
                .deleteCharAt(sql.length() - 1)
                .append(");");

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(String.valueOf(sql));
            for (String column : selectedColumnList) {
                Object value = ReflectUtils.getPkValueByName(obj, column);
                preparedStatement.setObject(selectedColumnList.indexOf(column) + 1, value);
            }
            preparedStatement.execute();
            log.debug(sql.toString());
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    /**
     * 批量新增
     *
     * @param list list
     */
    public void saveBatch(List<T> list) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        StringBuilder sql = new StringBuilder("Insert INTO " + tableName + "(");
        Map<String, String> columns = ReflectUtils.getColumnName(clazz);
        List<String> columnsStringList = new Vector<>(columns.values());
        List<String> selectedColumnList = new Vector<>();
        for (String column : columnsStringList) {
            Object value = ReflectUtils.getPkValueByName(list.getFirst(), column);
            if (value != null) {
                sql.append(column).append(",");
                selectedColumnList.add(column);
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") values (");
        sql.append("?,".repeat(selectedColumnList.size())).deleteCharAt(sql.length() - 1).append(");");

        try {
            // 开启事务
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(String.valueOf(sql));
            for (T obj : list) {
                for (String column : selectedColumnList) {
                    Object value = ReflectUtils.getPkValueByName(obj, column);
                    preparedStatement.setObject(selectedColumnList.indexOf(column) + 1, value);
                }
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            log.debug(sql.toString());
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    public void saveOrUpdate(T entity) {
        Connection conn = JDBCUtils.getConnection();
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        Object primaryKeyValue = ReflectUtils.getPkValueByName(entity, primaryKeyName);

        // 检查是否存在该记录
        String selectSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + primaryKeyName + "=?";
        try (PreparedStatement selectStmt = conn.prepareStatement(selectSql)) {
            selectStmt.setObject(1, primaryKeyValue);
            ResultSet rs = selectStmt.executeQuery();
            if (rs.next()) {
                long count = rs.getLong(1);
                if (count > 0) {
                    // 存在记录，执行更新操作
                    updateByPrimaryKey(entity);
                } else {
                    // 不存在记录，执行插入操作
                    save(entity);
                }
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    public void saveOrUpdateBatch(List<T> entities) {
        Connection conn = JDBCUtils.getConnection();
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        List<T> updateEntities = new Vector<>();
        List<T> insertEntities = new Vector<>();

        for (T entity : entities) {
            Object primaryKeyValue = ReflectUtils.getPkValueByName(entity, primaryKeyName);

            // 检查是否存在该记录
            String selectSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + primaryKeyName + "=?";
            try (PreparedStatement selectStmt = conn.prepareStatement(selectSql)) {
                selectStmt.setObject(1, primaryKeyValue);
                ResultSet rs = selectStmt.executeQuery();
                if (rs.next()) {
                    long count = rs.getLong(1);
                    if (count > 0) {
                        // 存在记录，执行更新操作
                        updateEntities.add(entity);
                    } else {
                        // 不存在记录，执行插入操作
                        insertEntities.add(entity);
                    }
                }
            } catch (Exception e) {
                log.debug(e.getMessage());
            }
        }

        if (!updateEntities.isEmpty()) {
            updateBatch(updateEntities);
        }

        if (!insertEntities.isEmpty()) {
            saveBatch(insertEntities);
        }
    }

    /**
     * 根据主键删除
     *
     * @param key key
     */
    public void removeByPrimaryKey(Integer key) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        String sql = "delete from " + tableName + " where " + primaryKeyName + "=?;";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, key);
            preparedStatement.execute();
            log.debug(sql);
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    /**
     * 根据主键更新
     *
     * @param obj obj
     */
    public void updateByPrimaryKey(T obj) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        StringBuilder sql = new StringBuilder("update " + tableName + " set ");
        Map<String, String> columns = ReflectUtils.getColumnName(clazz);
        List<String> columnsStringList = new Vector<>(columns.values());
        List<String> selectedColumnList = new Vector<>();
        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        Object primaryKeyValue = ReflectUtils.getPkValueByName(obj, primaryKeyName);
        for (String column : columnsStringList) {
            Object value = ReflectUtils.getPkValueByName(obj, column);
            if (value != null) {
                if (!Objects.equals(column, primaryKeyName)) {
                    sql.append(column).append("=?,");
                    selectedColumnList.add(column);
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1).append(" ");
        sql.append("where ").append(primaryKeyName).append("=?;");

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(String.valueOf(sql));
            for (String column : selectedColumnList) {
                Object value = ReflectUtils.getPkValueByName(obj, column);
                preparedStatement.setObject(selectedColumnList.indexOf(column) + 1, value);
            }
            preparedStatement.setObject(selectedColumnList.size() + 1, primaryKeyValue);
            preparedStatement.execute();
            log.debug(sql.toString());
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    /**
     * 根据主键批量更新
     *
     * @param entities 实体类集合
     */
    public void updateBatch(List<T> entities) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        StringBuilder sql = new StringBuilder("update " + tableName + " Set ");
        Map<String, String> columns = ReflectUtils.getColumnName(clazz);
        List<String> columnsStringList = new Vector<>(columns.values());
        List<String> selectedColumnList = new Vector<>();
        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        Object primaryKeyValue = ReflectUtils.getPkValueByName(entities.getFirst(), primaryKeyName);
        for (String column : columnsStringList) {
            Object value = ReflectUtils.getPkValueByName(entities.getFirst(), column);
            if (value != null) {
                if (!Objects.equals(column, primaryKeyName)) {
                    sql.append(" Set ").append(column).append("=?,");
                    selectedColumnList.add(column);
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append("where ").append(primaryKeyName).append("=?;");

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(String.valueOf(sql));
            for (T entity : entities) {
                for (String column : selectedColumnList) {
                    Object value = ReflectUtils.getPkValueByName(entity, column);
                    preparedStatement.setObject(selectedColumnList.indexOf(column) + 1, value);
                }
                preparedStatement.setObject(selectedColumnList.size() + 1, primaryKeyValue);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            log.debug(sql.toString());
        } catch (Exception e) {
            log.debug(e.getMessage());
        }
    }

    /**
     * 根据主键值查询
     *
     * @param id id
     */
    public T getByPrimaryKey(Integer id) {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");

        String primaryKeyName = ReflectUtils.getIdField(clazz).getName();
        String sql = "select * from " + tableName + " where " + primaryKeyName + "=?;";

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T entity = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                // 使用替代方法创建类的实例
                entity = clazz.getDeclaredConstructor().newInstance();
                // 获取类的所有字段
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true); // 确保可以访问私有字段
                    // 根据字段类型从 ResultSet 中获取数据并设置值
                    String columnName = StringUtils.camelToUnderScore(field.getName()); // 字段名转小驼峰
                    Object value = resultSet.getObject(columnName);
                    // 根据字段的实际类型进行适当的转换
                    field.set(entity, value);
                    // 清除列名和字段名一致的假设，使用正确的映射
                }
            }
            log.debug("SQL executed successfully: {}", sql);
        } catch (Exception e) {
            log.debug("Error executing SQL: {}", e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                log.error("Error closing resources: {}", e.getMessage());
            }
        }
        return entity;
    }

    /**
     * 查询所有
     *
     * @return {@code List<T> }
     */
    public List<T> list() {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String sql = "select * from " + tableName + ";";
        List<T> entities = new Vector<>();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                // 使用替代方法创建类的实例
                T entity = clazz.getDeclaredConstructor().newInstance();
                // 获取类的所有字段
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true); // 确保可以访问私有字段
                    // 根据字段类型从 ResultSet 中获取数据并设置值
                    String columnName = StringUtils.camelToUnderScore(field.getName()); // 字段名转小驼峰
                    Object value = resultSet.getObject(columnName);
                    // 根据字段的实际类型进行适当的转换
                    field.set(entity, value);
                    // 清除列名和字段名一致的假设，使用正确的映射
                }
                entities.add(entity);
            }
            log.debug("SQL executed successfully: {}", sql);
        } catch (Exception e) {
            log.error("Error executing SQL: " + e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                log.error("Error closing resources: " + e.getMessage());
            }
        }
        return entities;
    }

    private void ResetAutoIncrementPrimaryKey() {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String sql = "alter table " + tableName + " AUTO_INCREMENT = 1;";
        Execute(sql);
    }

    private void TruncateTable() {
        String tableName = ReflectUtils.getTableName(clazz).get("tableName");
        String sql = "truncate table " + tableName + ";";
        Execute(sql);
    }

    private void Execute(Object sql) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
            preparedStatement.execute();
            log.debug("SQL executed successfully: {}", sql);
        } catch (Exception e) {
            log.error("Error executing SQL: {}", e.getMessage());
        }
    }

}
